package com.exam.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.exam.entity.Classes;
import com.exam.entity.Role;
import com.exam.entity.User;
import com.exam.util.DBUtil;

import sun.nio.ch.SelChImpl;


public class UserDao {
	//excel添加学生
	public int addUser(User user) throws SQLException {
		Connection con=DBUtil.getConnection();
		String sql="insert into user(role_id,class_id,user_name,user_password,user_phone,user_sex) values(?,?,?,?,?,?)";
		QueryRunner runner = new QueryRunner();
		Object param[] = new Object[6];
		param[0]=user.getRole_id();
		param[1]=user.getClass_id();
		param[2]=user.getUser_name();
		param[3]=user.getUser_password();
		param[4]=user.getUser_phone();
		param[5]=user.getUser_sex();
		int ok = runner.update(con,sql,param);
		con.close();
		return ok;
	}
	//excel表格下载时查询学生数据
	public List sel() throws SQLException {
		Connection conn=  DBUtil.getConnection();
		String sql="select * from user";
		QueryRunner runner = new QueryRunner();
		List list =runner.query(conn,sql,new BeanListHandler<User>(User.class));
		conn.close(); 
		return list;
	}
	
	/*---------------------修改----------------------*/
	//修改时通过userid查用户
	public List<User> selUser(int user_id ) throws SQLException {
		Connection conn=DBUtil.getConnection();
		List<User> list=new ArrayList<User>();
		String sql="SELECT * FROM user where user_id=?";
		PreparedStatement pt=conn.prepareStatement(sql);
		String id=String.valueOf(user_id);
		pt.setString(1, id);
		ResultSet rs=pt.executeQuery();
		
		while(rs.next()){
			int userid=rs.getInt(1);
			int role_id=rs.getInt(2);
			int class_id=rs.getInt(3);
			String user_name=rs.getString(4);
			String user_password=rs.getString(5);
			String user_phone=rs.getString(6);
			String user_sex=rs.getString(7);
			String user_wx=rs.getString(8);
			String user_desc=rs.getString(9);
			String user_avatar=rs.getString(10);
			User user=new User(userid,role_id,class_id,user_name,user_password,user_phone,user_sex,user_wx,user_desc,user_avatar);
			list.add(user);
		}
		conn.close();
		return list;
	}
	//修改时通过roleid查role名
	public String selRole(int roleid ) throws SQLException {
		Connection conn=DBUtil.getConnection();
		String sql="SELECT role_name FROM role WHERE role_id=?";
		PreparedStatement pt=conn.prepareStatement(sql);
		String id=String.valueOf(roleid);
		pt.setString(1, id);
		ResultSet rs=pt.executeQuery();
		
		String role_name=null;
		while(rs.next()){
			role_name=rs.getString(1);
		}		
		return role_name;
	}
	//修改时通过classid查class名
	public String selClass(int class_id ) throws SQLException {
		Connection conn=DBUtil.getConnection();
		String sql="SELECT class_name FROM classes WHERE class_id=?";
		PreparedStatement pt=conn.prepareStatement(sql);
		String id=String.valueOf(class_id);
		pt.setString(1, id);
		ResultSet rs=pt.executeQuery();
		
		String class_name=null;
		while(rs.next()){
			class_name=rs.getString(1);
		}		
		return class_name;
	}
	//修改用户信息
	public int modifyUser(User user) throws SQLException {
        Connection conn=DBUtil.getConnection();
        String sql="UPDATE user SET role_id=?, class_id=?, user_name=?, user_password=?," + 
        		"user_phone=?, user_sex=?, user_wx=?, user_desc=?, user_avatar=? where user_id=?";
        QueryRunner runner = new QueryRunner();
		Object param[] = new Object[10];
		param[0]=user.getRole_id();
		param[1]=user.getClass_id();
		param[2]=user.getUser_name();
		param[3]=user.getUser_password();
		param[4]=user.getUser_phone();
		param[5]=user.getUser_sex();
		param[6]=user.getUser_wx();
		param[7]=user.getUser_desc();
		param[8]=user.getUser_avatar();
		param[9]=user.getUser_id();
		int ok = runner.update(conn,sql,param);
		conn.close();
		return ok;
	}
	/* ----------------注册------------------  */
	//用户注册
	public int registerUser(User user) throws SQLException {
        Connection conn=DBUtil.getConnection();
        String sql="insert into user(role_id, class_id, user_name, user_password, user_phone, user_sex,"
        		+ " user_wx, user_desc, user_avatar) values (?,?,?,?,?,?,?,?,?)";
        QueryRunner runner = new QueryRunner();
		Object param[] = new Object[9];
		param[0]=user.getRole_id();
		param[1]=user.getClass_id();
		param[2]=user.getUser_name();
		param[3]=user.getUser_password();
		param[4]=user.getUser_phone();
		param[5]=user.getUser_sex();
		param[6]=user.getUser_wx();
		param[7]=user.getUser_desc();
		param[8]=user.getUser_avatar();
		int ok = runner.update(conn,sql,param);
		conn.close();
		return ok;
	}
	//注册时查role名
	public List selRole() throws SQLException {
		Connection conn=DBUtil.getConnection();
		String sql="SELECT * FROM role";
		PreparedStatement pt=conn.prepareStatement(sql);
		QueryRunner runner = new QueryRunner();
		List list =runner.query(conn,sql,new BeanListHandler<Role>(Role.class));
		conn.close(); 
		return list;
	}
	//注册时查class名
	public List selClass() throws SQLException {
		Connection conn=DBUtil.getConnection();
		String sql="SELECT * FROM classes";
		QueryRunner runner = new QueryRunner();
		List list =runner.query(conn,sql,new BeanListHandler<Classes>(Classes.class));
		conn.close(); 
		return list;
	}
	
	/*------------------分页查询----------------------*/
	//查出所有用户 --要分页查询
	public List show(int page) throws SQLException {
		//固定每页10条
		Connection conn=  DBUtil.getConnection();
		int temp  = (page-1)*10;
		String sql="select * from user where role_id =3 limit ?,10";
		QueryRunner runner = new QueryRunner();
		List list =runner.query(conn,sql,temp,new BeanListHandler<User>(User.class));
		conn.close(); 
		return list;
	}
	// 学生的总人数
	public int count() throws SQLException {
		Connection conn=  DBUtil.getConnection();
		String sql="select count(*) from user where role_id =3";
		QueryRunner runner = new QueryRunner();
		Long n  = runner.query(conn, sql, new ScalarHandler<Long>());
		conn.close(); 
		return n.intValue();
	}
	
	/*----------------------删除-----------------------*/
	//通过id删除
	public int del(int uid ) throws SQLException {
		Connection conn=  DBUtil.getConnection();
		String sql="delete from user where user_id =?";	 
		QueryRunner runner = new QueryRunner();
		int n = runner.update(conn, sql, uid);
		conn.close();
		return n;
	}
	
}
